package com.rankeiot.core.excel;

import ch.rabanti.nanoxlsx4j.*;
import ch.rabanti.nanoxlsx4j.exceptions.FormatException;

import ch.rabanti.nanoxlsx4j.lowLevel.Packer;

import ch.rabanti.nanoxlsx4j.styles.*;
import org.w3c.dom.Document;
import org.xml.sax.InputSource;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.*;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import java.io.*;
import java.math.BigDecimal;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * 流式写入数据到Excel
 */
public class XlsxStreamWriter implements Closeable {
    private final ZipOutputStream out;
    private final Workbook workbook;
    private List<Packer.Relationship> relationships = new ArrayList<>();
    private final SortedMap sharedStrings;
    private int sharedStringsTotalCount;

    private SheetWriter currentSheetWriter;


    private final List<Boolean> includeContentType = new ArrayList<>();
    private final List<String> pathList = new ArrayList<>();
    private final List<String> contentTypeList = new ArrayList<>();
    private StyleManager styles;
    private boolean flashed=false;
    public XlsxStreamWriter(OutputStream outputStream) {
        sharedStrings = new SortedMap();
        sharedStringsTotalCount = 0;
        out = new ZipOutputStream(new BufferedOutputStream(outputStream), Charset.forName("UTF-8"));
        out.setMethod(ZipOutputStream.DEFLATED);
        workbook = new Workbook(false);
        this.styles = StyleManager.getManagedStyles(workbook);
    }
    public XlsxStreamWriter(OutputStream outputStream,Workbook workbook) {
        sharedStrings = new SortedMap();
        sharedStringsTotalCount = 0;
        out = new ZipOutputStream(new BufferedOutputStream(outputStream), Charset.forName("UTF-8"));
        out.setMethod(ZipOutputStream.DEFLATED);
        this.workbook =  new Workbook(false);;
        this.styles = StyleManager.getManagedStyles(workbook);
    }
    public SheetWriter createSheet(String name) throws IOException {
        if (currentSheetWriter != null) {
            currentSheetWriter.end();
        }
        workbook.addWorksheet(name);
        currentSheetWriter = new SheetWriter(workbook.getCurrentWorksheet());
        return currentSheetWriter;
    }
    public SheetWriter createSheet(Worksheet sheet) throws IOException {
        if (currentSheetWriter != null) {
            currentSheetWriter.end();
        }
       // workbook.addWorksheet(name);
        workbook.addWorksheet(sheet);

        currentSheetWriter = new SheetWriter(workbook.getCurrentWorksheet());
        return currentSheetWriter;
    }

    public SheetWriter getCurrentSheetWriter() {
        return currentSheetWriter;
    }
    public static List<Cell> toDefaultRow(Style style,Object ...values){
        List<Cell> cells = new ArrayList<>(values.length);
        for (Object v : values) {
            Cell c=new Cell(v, Cell.CellType.DEFAULT);
            if(style!=null){
                c.setStyle(style);
            }
            cells.add(c);
        }
        return cells;
    }
    public void flush() throws IOException {
        if(flashed){
            return;
        }else{
            flashed=true;
        }
        if (currentSheetWriter != null) {
            currentSheetWriter.end();
        }
        Document app = createAppPropertiesDocument();
        addPart("docProps/app.xml", "application/vnd.openxmlformats-officedocument.extended-properties+xml", app);
        Document core = createCorePropertiesDocument();
        addPart("docProps/core.xml", "application/vnd.openxmlformats-package.core-properties+xml", core);
        Document styles = createStyleSheetDocument();
        addPart("xl/styles.xml", "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml", styles);
        Document book = createWorkbookDocument();
        addPart("xl/workbook.xml", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", book, false);
        addPart("xl/sharedStrings.xml", "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml", createSharedStringsDocument());
        Relationship rel = new Relationship("_rels/.rels");
        rel.addRelationshipEntry("/xl/workbook.xml", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument");
        rel.addRelationshipEntry("/docProps/core.xml", "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties");
        rel.addRelationshipEntry("/docProps/app.xml", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties");
        addPart(rel);
        rel = new Relationship("xl/_rels/workbook.xml.rels");
        for (int i = 0; i < this.workbook.getWorksheets().size(); i++) {
            Worksheet sheet = this.workbook.getWorksheets().get(i);
            String file = "sheet" + sheet.getSheetID() + ".xml";
            rel.addRelationshipEntry("/xl/worksheets/" + file, "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet");
            addPart("xl/worksheets/" + file, "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");
        }
        rel.addRelationshipEntry("/xl/styles.xml", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles");
        rel.addRelationshipEntry("/xl/sharedStrings.xml", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings");
        addPart(rel);
        byte[] contentTypes = createContentTypeDocument();
        addPart("[Content_Types].xml", contentTypes);
        out.flush();
    }

    private void addPart(String name, byte[] data) throws IOException {
        ZipEntry entry = new ZipEntry(name);
        out.putNextEntry(entry);
        out.write(data, 0, data.length);
    }

    private void addPart(Relationship rel) throws IOException {
        byte[] data = createRelationshipDocument(rel);
        ZipEntry entry = new ZipEntry(rel.getRootFolder());
        out.putNextEntry(entry);
        out.write(data, 0, data.length);
    }

    private void addPart(String path, String contentType) {
        pathList.add(path);
        contentTypeList.add(contentType);
        includeContentType.add(true);
    }

    private void addPart(String path, String contentType, Document doc) throws IOException {
        addPart(path, contentType, doc, true);
    }

    private void addPart(String path, String contentType, Document doc, boolean includeInContentType) throws IOException {
        pathList.add(path);
        contentTypeList.add(contentType);
        includeContentType.add(includeInContentType);
        ZipEntry entry = new ZipEntry(path);
        out.putNextEntry(entry);
        out.write(createBytesFromDocument(doc));
    }

    /**
     * Method to create shared strings as XML document
     *
     * @return Formatted XML document
     * @throws ch.rabanti.nanoxlsx4j.exceptions.IOException Thrown in case of an error while creating the XML document
     */
    private Document createSharedStringsDocument() {
        StringBuilder sb = new StringBuilder();
        sb.append("<sst xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" count=\"");
        sb.append(this.sharedStringsTotalCount);
        sb.append("\" uniqueCount=\"");
        sb.append(this.sharedStrings.size());
        sb.append("\">");
        ArrayList<String> keys = this.sharedStrings.getKeys();
        //for (Map.Entry<String, String> str : sharedStrings.entrySet())
        for (int i = 0; i < keys.size(); i++) {
            sb.append("<si><t>");
            //sb.append(escapeXMLChars(str.getKey()));
            sb.append(escapeXMLChars(keys.get(i)));
            sb.append("</t></si>");
        }
        sb.append("</sst>");
        return createXMLDocument(sb.toString(), "SHAREDSTRINGS");
    }

    private Document createWorkbookDocument() {
        if (this.workbook.getWorksheets().isEmpty()) {
            throw new RuntimeException("UnknownRangeException:The workbook can not be created because no worksheet was defined.");
        }
        StringBuilder sb = new StringBuilder();
        sb.append("<workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\">");
        if (this.workbook.getSelectedWorksheet() > 0) {
            sb.append("<bookViews><workbookView activeTab=\"");
            sb.append(this.workbook.getSelectedWorksheet());
            sb.append("\"/></bookViews>");
        }
        if (this.workbook.isWorkbookProtectionUsed() == true) {
            sb.append("<workbookProtection");
            if (this.workbook.isWindowsLockedIfProtected() == true) {
                sb.append(" lockWindows=\"1\"");
            }
            if (this.workbook.isStructureLockedIfProtected() == true) {
                sb.append(" lockStructure=\"1\"");
            }
            if (Helper.isNullOrEmpty(this.workbook.getWorkbookProtectionPassword()) == false) {
                sb.append("workbookPassword=\"");
                sb.append(generatePasswordHash(this.workbook.getWorkbookProtectionPassword()));
                sb.append("\"");
            }
            sb.append("/>");
        }
        sb.append("<sheets>");
        int id;
        for (int i = 0; i < this.workbook.getWorksheets().size(); i++) {
            id = this.workbook.getWorksheets().get(i).getSheetID();
            sb.append("<sheet r:id=\"rId");
            sb.append(id);
            sb.append("\" sheetId=\"");
            sb.append(id);
            sb.append("\" name=\"").append(escapeXMLAttributeChars(this.workbook.getWorksheets().get(i).getSheetName())).append("\"/>");
        }
        sb.append("</sheets>");
        sb.append("</workbook>");
        return createXMLDocument(sb.toString(), "WORKBOOK");
    }

    /**
     * Method to escape XML characters in an XML attribute
     *
     * @param input Input string to process
     * @return Escaped string
     */
    private static String escapeXMLAttributeChars(String input) {
        input = escapeXMLChars(input); // Sanitize string from illegal characters beside quotes
        input = input.replace("\"", "&quot;");
        return input;
    }



    /**
     * Method to create the XML string for the color-MRU part of the style sheet
     * document (recent colors)
     *
     * @return String with formatted XML data
     */
    private String createMruColorsString() {
        StringBuilder sb = new StringBuilder();
        List<String> tempColors = new ArrayList<>();
        for (String item : this.workbook.getMruColors()) {
            if (item.equals(Fill.DEFAULT_COLOR)) {
                continue;
            }
            if (!tempColors.contains(item)) {
                tempColors.add(item);
            }
        }
        if (tempColors.size() > 0) {
            sb.append("<mruColors>");
            for (int i = 0; i < tempColors.size(); i++) {
                sb.append("<color rgb=\"").append(tempColors.get(i)).append("\"/>");
            }
            sb.append("</mruColors>");
            return sb.toString();
        }
        else {
            return "";
        }
    }

    private Document createStyleSheetDocument() {
        String bordersString = createStyleBorderString();
        String fillsString = createStyleFillString();
        String fontsString = createStyleFontString();
        String numberFormatsString = createStyleNumberFormatString();
        int numFormatCount = getNumberFormatStringCounter();
        String xfsStings = createStyleXfsString();
        String mruColorString = createMruColorsString();
        StringBuilder sb = new StringBuilder();

        sb.append(
                "<styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" mc:Ignorable=\"x14ac\" xmlns:x14ac=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac\">");
        if (numFormatCount > 0) {
            sb.append("<numFmts count=\"");
            sb.append(numFormatCount);
            sb.append("\">");
            sb.append(numberFormatsString).append("</numFmts>");
        }
        sb.append("<fonts x14ac:knownFonts=\"1\" count=\"");
        sb.append(this.styles.getFontStyleNumber());
        sb.append("\">");
        sb.append(fontsString).append("</fonts>");
        sb.append("<fills count=\"");
        sb.append(this.styles.getFillStyleNumber());
        sb.append("\">");
        sb.append(fillsString).append("</fills>");
        sb.append("<borders count=\"");
        sb.append(this.styles.getBorderStyleNumber());
        sb.append("\">");
        sb.append(bordersString).append("</borders>");
        sb.append("<cellXfs count=\"");
        sb.append(this.styles.getStyleNumber());
        sb.append("\">");
        sb.append(xfsStings).append("</cellXfs>");
        if (this.workbook.getWorkbookMetadata() != null) {
            if (!Helper.isNullOrEmpty(mruColorString)) {
                sb.append("<colors>");
                sb.append(mruColorString);
                sb.append("</colors>");
            }
        }
        sb.append("</styleSheet>");
        return createXMLDocument(sb.toString());
    }

    /**
     * Method to create the XML string for the border part of the style sheet
     * document
     *
     * @return String with formatted XML data
     */
    private String createStyleBorderString() {
        Border[] borderStyles = this.styles.getBorders();
        StringBuilder sb = new StringBuilder();
        for (Border borderStyle : borderStyles) {
            if (borderStyle.isDiagonalDown() && !borderStyle.isDiagonalUp()) {
                sb.append("<border diagonalDown=\"1\">");
            }
            else if (!borderStyle.isDiagonalDown() && borderStyle.isDiagonalUp()) {
                sb.append("<border diagonalUp=\"1\">");
            }
            else if (borderStyle.isDiagonalDown() && borderStyle.isDiagonalUp()) {
                sb.append("<border diagonalDown=\"1\" diagonalUp=\"1\">");
            }
            else {
                sb.append("<border>");
            }
            if (borderStyle.getLeftStyle() != Border.StyleValue.none) {
                sb.append("<left style=\"").append(Border.getStyleName(borderStyle.getLeftStyle())).append("\">");
                if (!Helper.isNullOrEmpty(borderStyle.getLeftColor())) {
                    sb.append("<color rgb=\"").append(borderStyle.getLeftColor()).append("\"/>");
                }
                else {
                    sb.append("<color auto=\"1\"/>");
                }
                sb.append("</left>");
            }
            else {
                sb.append("<left/>");
            }
            if (borderStyle.getRightStyle() != Border.StyleValue.none) {
                sb.append("<right style=\"").append(Border.getStyleName(borderStyle.getRightStyle())).append("\">");
                if (!Helper.isNullOrEmpty(borderStyle.getRightColor())) {
                    sb.append("<color rgb=\"").append(borderStyle.getRightColor()).append("\"/>");
                }
                else {
                    sb.append("<color auto=\"1\"/>");
                }
                sb.append("</right>");
            }
            else {
                sb.append("<right/>");
            }
            if (borderStyle.getTopStyle() != Border.StyleValue.none) {
                sb.append("<top style=\"").append(Border.getStyleName(borderStyle.getTopStyle())).append("\">");
                if (!Helper.isNullOrEmpty(borderStyle.getTopColor())) {
                    sb.append("<color rgb=\"").append(borderStyle.getTopColor()).append("\"/>");
                }
                else {
                    sb.append("<color auto=\"1\"/>");
                }
                sb.append("</top>");
            }
            else {
                sb.append("<top/>");
            }
            if (borderStyle.getBottomStyle() != Border.StyleValue.none) {
                sb.append("<bottom style=\"").append(Border.getStyleName(borderStyle.getBottomStyle())).append("\">");
                if (!Helper.isNullOrEmpty(borderStyle.getBottomColor())) {
                    sb.append("<color rgb=\"").append(borderStyle.getBottomColor()).append("\"/>");
                }
                else {
                    sb.append("<color auto=\"1\"/>");
                }
                sb.append("</bottom>");
            }
            else {
                sb.append("<bottom/>");
            }
            if (borderStyle.getDiagonalStyle() != Border.StyleValue.none) {
                sb.append("<diagonal style=\"").append(Border.getStyleName(borderStyle.getDiagonalStyle())).append("\">");
                if (!Helper.isNullOrEmpty(borderStyle.getDiagonalColor())) {
                    sb.append("<color rgb=\"").append(borderStyle.getDiagonalColor()).append("\"/>");
                }
                else {
                    sb.append("<color auto=\"1\"/>");
                }
                sb.append("</diagonal>");
            }
            else {
                sb.append("<diagonal/>");
            }
            sb.append("</border>");
        }
        return sb.toString();
    }

    /**
     * Method to create the XML string for the fill part of the style sheet document
     *
     * @return String with formatted XML data
     */
    private String createStyleFillString() {
        Fill[] fillStyles = this.styles.getFills();
        StringBuilder sb = new StringBuilder();
        for (Fill fillStyle : fillStyles) {
            sb.append("<fill>");
            sb.append("<patternFill patternType=\"").append(Fill.getPatternName(fillStyle.getPatternFill())).append("\"");
            if (fillStyle.getPatternFill() == Fill.PatternValue.solid) {
                sb.append(">");
                sb.append("<fgColor rgb=\"").append(fillStyle.getForegroundColor()).append("\"/>");
                sb.append("<bgColor indexed=\"");
                sb.append(fillStyle.getIndexedColor());
                sb.append("\"/>");
                sb.append("</patternFill>");
            }
            else if (fillStyle.getPatternFill() == Fill.PatternValue.mediumGray ||
                    fillStyle.getPatternFill() == Fill.PatternValue.lightGray ||
                    fillStyle.getPatternFill() == Fill.PatternValue.gray0625 ||
                    fillStyle.getPatternFill() == Fill.PatternValue.darkGray) {
                sb.append(">");
                sb.append("<fgColor rgb=\"").append(fillStyle.getForegroundColor()).append("\"/>");
                if (!Helper.isNullOrEmpty(fillStyle.getBackgroundColor())) {
                    sb.append("<bgColor rgb=\"").append(fillStyle.getBackgroundColor()).append("\"/>");
                }
                sb.append("</patternFill>");
            }
            else {
                sb.append("/>");
            }
            sb.append("</fill>");
        }
        return sb.toString();
    }

    /**
     * Method to create the XML string for the font part of the style sheet document
     *
     * @return String with formatted XML data
     */
    private String createStyleFontString() {
        Font[] fontStyles = this.styles.getFonts();
        StringBuilder sb = new StringBuilder();
        for (Font fontStyle : fontStyles) {
            sb.append("<font>");
            if (fontStyle.isBold()) {
                sb.append("<b/>");
            }
            if (fontStyle.isItalic()) {
                sb.append("<i/>");
            }
            if (fontStyle.isStrike()) {
                sb.append("<strike/>");
            }
            if (fontStyle.getUnderline() == null || fontStyle.getUnderline() != Font.UnderlineValue.none) {
                if (fontStyle.getUnderline() == Font.UnderlineValue.u_double) {
                    sb.append("<u val=\"double\"/>");
                }
                else if (fontStyle.getUnderline() == Font.UnderlineValue.singleAccounting) {
                    sb.append("<u val=\"singleAccounting\"/>");
                }
                else if (fontStyle.getUnderline() == Font.UnderlineValue.doubleAccounting) {
                    sb.append("<u val=\"doubleAccounting\"/>");
                }
                else {
                    sb.append("<u/>");
                }
            }
            if (fontStyle.getVerticalAlign() == Font.VerticalAlignValue.subscript) {
                sb.append("<vertAlign val=\"subscript\"/>");
            }
            else if (fontStyle.getVerticalAlign() == Font.VerticalAlignValue.superscript) {
                sb.append("<vertAlign val=\"superscript\"/>");
            }
            sb.append("<sz val=\"");
            sb.append(fontStyle.getSize());
            sb.append("\"/>");
            if (Helper.isNullOrEmpty(fontStyle.getColorValue())) {
                sb.append("<color theme=\"");
                sb.append(fontStyle.getColorTheme());
                sb.append("\"/>");
            }
            else {
                sb.append("<color rgb=\"").append(fontStyle.getColorValue()).append("\"/>");
            }
            sb.append("<name val=\"").append(fontStyle.getName()).append("\"/>");
            sb.append("<family val=\"").append(fontStyle.getFamily()).append("\"/>");
            if (fontStyle.getScheme() != Font.SchemeValue.none) {
                if (fontStyle.getScheme() == Font.SchemeValue.major) {
                    sb.append("<scheme val=\"major\"/>");
                }
                else if (fontStyle.getScheme() == Font.SchemeValue.minor) {
                    sb.append("<scheme val=\"minor\"/>");
                }
            }
            if (!Helper.isNullOrEmpty(fontStyle.getCharset())) {
                sb.append("<charset val=\"").append(fontStyle.getCharset()).append("\"/>");
            }
            sb.append("</font>");
        }
        return sb.toString();
    }
    /**
     * Method to create the XML string for the number format part of the style sheet
     * document
     *
     * @return String with formatted XML data
     */
    private String createStyleNumberFormatString() {
        NumberFormat[] numberFormatStyles = this.styles.getNumberFormats();
        StringBuilder sb = new StringBuilder();
        for (NumberFormat numberFormatStyle : numberFormatStyles) {
            if (numberFormatStyle.isCustomFormat()) {

                if (Helper.isNullOrEmpty(numberFormatStyle.getCustomFormatCode())) {
                    throw new FormatException(
                            "The number format style component with the ID " + numberFormatStyle.getCustomFormatID() + " cannot be null or empty");
                }
                sb.append("<numFmt formatCode=\"").append(escapeXMLAttributeChars(numberFormatStyle.getCustomFormatCode())).append("\" numFmtId=\"")
                        .append(numberFormatStyle.getCustomFormatID()).append("\"/>");
            }
        }
        return sb.toString();
    }

    private String createStyleXfsString() {
        Style[] styleItems = this.styles.getStyles();
        StringBuilder sb = new StringBuilder();
        StringBuilder sb2;
        String alignmentString, protectionString;
        int formatNumber, textRotation;
        for (Style style : styleItems) {
            textRotation = style.getCellXf().calculateInternalRotation();
            alignmentString = "";
            protectionString = "";
            if (style.getCellXf().getHorizontalAlign() != CellXf.HorizontalAlignValue.none ||
                    style.getCellXf().getVerticalAlign() != CellXf.VerticalAlignValue.none ||
                    style.getCellXf().getAlignment() != CellXf.TextBreakValue.none ||
                    textRotation != 0) {
                sb2 = new StringBuilder();
                sb2.append("<alignment");
                if (style.getCellXf().getHorizontalAlign() != CellXf.HorizontalAlignValue.none) {
                    sb2.append(" horizontal=\"");
                    if (style.getCellXf().getHorizontalAlign() == CellXf.HorizontalAlignValue.center) {
                        sb2.append("center");
                    }
                    else if (style.getCellXf().getHorizontalAlign() == CellXf.HorizontalAlignValue.right) {
                        sb2.append("right");
                    }
                    else if (style.getCellXf().getHorizontalAlign() == CellXf.HorizontalAlignValue.centerContinuous) {
                        sb2.append("centerContinuous");
                    }
                    else if (style.getCellXf().getHorizontalAlign() == CellXf.HorizontalAlignValue.distributed) {
                        sb2.append("distributed");
                    }
                    else if (style.getCellXf().getHorizontalAlign() == CellXf.HorizontalAlignValue.fill) {
                        sb2.append("fill");
                    }
                    else if (style.getCellXf().getHorizontalAlign() == CellXf.HorizontalAlignValue.general) {
                        sb2.append("general");
                    }
                    else if (style.getCellXf().getHorizontalAlign() == CellXf.HorizontalAlignValue.justify) {
                        sb2.append("justify");
                    }
                    else {
                        sb2.append("left");
                    }
                    sb2.append("\"");
                }
                if (style.getCellXf().getVerticalAlign() != CellXf.VerticalAlignValue.none) {
                    sb2.append(" vertical=\"");
                    if (style.getCellXf().getVerticalAlign() == CellXf.VerticalAlignValue.center) {
                        sb2.append("center");
                    }
                    else if (style.getCellXf().getVerticalAlign() == CellXf.VerticalAlignValue.distributed) {
                        sb2.append("distributed");
                    }
                    else if (style.getCellXf().getVerticalAlign() == CellXf.VerticalAlignValue.justify) {
                        sb2.append("justify");
                    }
                    else if (style.getCellXf().getVerticalAlign() == CellXf.VerticalAlignValue.top) {
                        sb2.append("top");
                    }
                    else {
                        sb2.append("bottom");
                    }
                    sb2.append("\"");
                }
                if (style.getCellXf().getIndent() > 0 &&
                        (style.getCellXf().getHorizontalAlign() == CellXf.HorizontalAlignValue.left ||
                                style.getCellXf().getHorizontalAlign() == CellXf.HorizontalAlignValue.right ||
                                style.getCellXf().getHorizontalAlign() == CellXf.HorizontalAlignValue.distributed)) {
                    sb2.append(" indent=\"");
                    sb2.append(style.getCellXf().getIndent());
                    sb2.append("\"");
                }

                if (style.getCellXf().getAlignment() != CellXf.TextBreakValue.none) {
                    if (style.getCellXf().getAlignment() == CellXf.TextBreakValue.shrinkToFit) {
                        sb2.append(" shrinkToFit=\"1");
                    }
                    else {
                        sb2.append(" wrapText=\"1");
                    }
                    sb2.append("\"");
                }
                if (textRotation != 0) {
                    sb2.append(" textRotation=\"");
                    sb2.append(textRotation);
                    sb2.append("\"");
                }
                sb2.append("/>"); // </xf>
                alignmentString = sb2.toString();
            }
            if (style.getCellXf().isHidden() || style.getCellXf().isLocked()) {
                if (style.getCellXf().isHidden() && style.getCellXf().isLocked()) {
                    protectionString = "<protection locked=\"1\" hidden=\"1\"/>";
                }
                else if (style.getCellXf().isHidden() && !style.getCellXf().isLocked()) {
                    protectionString = "<protection hidden=\"1\" locked=\"0\"/>";
                }
                else {
                    protectionString = "<protection hidden=\"0\" locked=\"1\"/>";
                }
            }
            sb.append("<xf numFmtId=\"");
            if (style.getNumberFormat().isCustomFormat()) {
                sb.append(style.getNumberFormat().getCustomFormatID());
            }
            else {
                formatNumber = style.getNumberFormat().getNumber().getValue();
                sb.append(formatNumber);
            }
            sb.append("\" borderId=\"");
            sb.append(style.getBorder().getInternalID());
            sb.append("\" fillId=\"");
            sb.append(style.getFill().getInternalID());
            sb.append("\" fontId=\"");
            sb.append(style.getFont().getInternalID());
            if (!style.getFont().isDefaultFont()) {
                sb.append("\" applyFont=\"1");
            }
            if (style.getFill().getPatternFill() != Fill.PatternValue.none) {
                sb.append("\" applyFill=\"1");
            }
            if (!style.getBorder().isEmpty()) {
                sb.append("\" applyBorder=\"1");
            }
            if (!alignmentString.isEmpty() || style.getCellXf().isForceApplyAlignment()) {
                sb.append("\" applyAlignment=\"1");
            }
            if (!protectionString.isEmpty()) {
                sb.append("\" applyProtection=\"1");
            }
            if (style.getNumberFormat().getNumber() != NumberFormat.FormatNumber.none) {
                sb.append("\" applyNumberFormat=\"1\"");
            }
            else {
                sb.append("\"");
            }
            if (!alignmentString.isEmpty() || !protectionString.isEmpty()) {
                sb.append(">");
                sb.append(alignmentString);
                sb.append(protectionString);
                sb.append("</xf>");
            }
            else {
                sb.append("/>");
            }
        }
        return sb.toString();
    }

    private Document createCorePropertiesDocument() {
        StringBuilder sb = new StringBuilder();
        sb.append("<cp:coreProperties xmlns:cp=\"http://schemas.openxmlformats.org/package/2006/metadata/core-properties\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:dcterms=\"http://purl.org/dc/terms/\" xmlns:dcmitype=\"http://purl.org/dc/dcmitype/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">");
        sb.append(createCorePropertiesString());
        sb.append("</cp:coreProperties>");
        return createXMLDocument(sb.toString());
    }

    private String createCorePropertiesString() {
        if (this.workbook.getWorkbookMetadata() == null) {
            return "";
        }
        Metadata md = this.workbook.getWorkbookMetadata();
        StringBuilder sb = new StringBuilder();
        appendXMLtag(sb, md.getTitle(), "title", "dc");
        appendXMLtag(sb, md.getSubject(), "subject", "dc");
        appendXMLtag(sb, md.getCreator(), "creator", "dc");
        appendXMLtag(sb, md.getCreator(), "lastModifiedBy", "cp");
        appendXMLtag(sb, md.getKeywords(), "keywords", "cp");
        appendXMLtag(sb, md.getDescription(), "description", "dc");

        Calendar cal = new GregorianCalendar();
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss'Z'");
        df.setCalendar(cal);
        Date now = cal.getTime();
        String time = df.format(now);

        sb.append("<dcterms:created xsi:type=\"dcterms:W3CDTF\">").append(time).append("</dcterms:created>");
        sb.append("<dcterms:modified xsi:type=\"dcterms:W3CDTF\">").append(time).append("</dcterms:modified>");

        appendXMLtag(sb, md.getCategory(), "category", "cp");
        appendXMLtag(sb, md.getContentStatus(), "contentStatus", "cp");

        return sb.toString();
    }

    public static byte[] createBytesFromDocument(Document document) throws IOException {
        try {
            Transformer transformer = TransformerFactory.newInstance().newTransformer();
            transformer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
            ByteArrayOutputStream bs = new ByteArrayOutputStream();
            Result output = new StreamResult(bs);
            Source input = new DOMSource(document);

            transformer.transform(input, output);
            bs.flush();
            byte[] bytes = bs.toByteArray();
            bs.close();
            return bytes;
        } catch (Exception e) {
            throw new IOException("ByteSteamException:There was an error while creating the byte array. Please see the inner exception.", e);
        }
    }

    @Override
    public void close() throws IOException {
        flush();
        out.close();
    }

    private void write(String content) throws IOException {
        out.write(content.getBytes(StandardCharsets.UTF_8));
    }
    public class SheetWriter {
        Worksheet worksheet;
        int currentRow = 0;
        private boolean isEnd = false;

        public SheetWriter(Worksheet sheet) throws IOException {
            this.worksheet = sheet;
            String file = "xl/worksheets/sheet" + sheet.getSheetID() + ".xml";
            ZipEntry entry = new ZipEntry(file);
            out.putNextEntry(entry);
            write("<?xml version=\"1.0\" encoding=\"utf-8\" standalone=\"yes\"?>");
            write("<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" mc:Ignorable=\"x14ac\" xmlns:x14ac=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac\">");
            write("<sheetFormatPr x14ac:dyDescent=\"0.25\" defaultRowHeight=\"");
            write(worksheet.getDefaultRowHeight() + "");
            write("\" baseColWidth=\"");
            write(worksheet.getDefaultColumnWidth() + "");
            write("\"/>");
            write("<sheetData>");

        }
        private String createMergedCellsString(Worksheet sheet) {
            if (sheet.getMergedCells().size() < 1) {
                return "";
            }
            Iterator<Map.Entry<String, Range>> itr;
            Map.Entry<String, Range> range;
            StringBuilder sb = new StringBuilder();
            sb.append("<mergeCells count=\"").append(sheet.getMergedCells().size()).append("\">");
            itr = sheet.getMergedCells().entrySet().iterator();
            while (itr.hasNext()) {
                range = itr.next();
                sb.append("<mergeCell ref=\"").append(range.getValue().toString()).append("\"/>");
            }
            sb.append("</mergeCells>");
            return sb.toString();
        }
        public void end() throws IOException {
            if (isEnd) {
                return;
            }
            write("</sheetData>");
            write(createMergedCellsString(worksheet));
            if (worksheet.getAutoFilterRange() != null) {
                write("<autoFilter ref=\"");
                write(worksheet.getAutoFilterRange().toString());
                write("\"/>");
            }
            write("</worksheet>");
            isEnd = true;
        }
        public void setMergedCells(Map<String, Range> mergedCells){
            this.worksheet.getMergedCells().putAll(mergedCells);
        }
        public void setAutoFilterRange(Range range){
            if(range!=null){
                this.worksheet.setAutoFilterRange(range.toString());
            }
        }
        public void writeRowCells(List<Cell> cells) throws IOException {
            if (isEnd) {
                return;
            }
            String line = createRowString(cells, worksheet);
            write(line);
            currentRow++;
        }

        public void addRow(List values) throws IOException {
           addRow(null,values);
        }
        public void addRow(Style style,List values) throws IOException {
            List<Cell> cells = new ArrayList<>(values.size());
            for (Object v : values) {
                Cell c=new Cell(v, Cell.CellType.DEFAULT);
                if(style!=null){
                    c.setStyle(style);
                }
                cells.add(c);
            }
            writeRowCells(cells);
        }
        public void addRow(Object... values) throws IOException {
            addRow(null,values);
        }
        public void addRow(Style style,Object... values) throws IOException {
            List<Cell> cells = toDefaultRow(style,values);
            writeRowCells(cells);
        }
        private String createRowString(List<Cell> columnFields, Worksheet worksheet) {
            int rowNumber = currentRow;
            String height = "";
            String hidden = "";
            if (worksheet.getRowHeights().containsKey(rowNumber)) {
                if (worksheet.getRowHeights().get(rowNumber) != worksheet.getDefaultRowHeight()) {
                    height = " x14ac:dyDescent=\"0.25\" customHeight=\"1\" ht=\"" + worksheet.getRowHeights().get(rowNumber) + "\"";
                }
            }
            if (worksheet.getHiddenRows().containsKey(rowNumber)) {
                if (worksheet.getHiddenRows().get(rowNumber) == true) {
                    hidden = " hidden=\"1\"";
                }
            }
            int colNum = columnFields.size();
            StringBuilder sb = new StringBuilder(43 * colNum); // A row string size is according to statistics (random value) 43 times the column number
            //StringBuilder sb = new StringBuilder();
            if (colNum > 0) {
                sb.append("<row r=\"");
                sb.append((rowNumber + 1));
                sb.append("\"").append(height).append(hidden).append(">");
            } else {
                sb.append("<row").append(height).append(">");
            }
            String typeAttribute;
            String sValue, tValue;
            String value = "";
            boolean bVal;

            Date dVal;
            int col = 0;
            Cell item;
            for (int i = 0; i < colNum; i++) {
                item = columnFields.get(i);
                item.setRowNumber(currentRow);
                item.setColumnNumber(i);
                tValue = " ";
                if (item.getCellStyle() != null) {
                    sValue = " s=\"" + item.getCellStyle().getInternalID() + "\" ";
                } else {
                    sValue = "";
                }
                item.resolveCellType(); // Recalculate the type (for handling DEFAULT)
                if (item.getDataType() == Cell.CellType.BOOL) {
                    typeAttribute = "b";
                    tValue = " t=\"" + typeAttribute + "\" ";
                    bVal = (boolean) item.getValue();
                    if (bVal == true) {
                        value = "1";
                    } else {
                        value = "0";
                    }

                }
                // Number casting
                else if (item.getDataType() == Cell.CellType.NUMBER) {
                    typeAttribute = "n";
                    tValue = " t=\"" + typeAttribute + "\" ";
                    Object o = item.getValue();
                    if (o instanceof Byte) {
                        value = Byte.toString((byte) item.getValue());
                    } else if (o instanceof BigDecimal) {
                        value = item.getValue().toString();
                    } else if (o instanceof Double) {
                        value = Double.toString((double) item.getValue());
                    } else if (o instanceof Float) {
                        value = Float.toString((float) item.getValue());
                    } else if (o instanceof Integer) {
                        value = Integer.toString((int) item.getValue());
                    } else if (o instanceof Long) {
                        value = Long.toString((long) item.getValue());
                    } else if (o instanceof Short) {
                        value = Short.toString((short) item.getValue());
                    }
                }
                // Date parsing
                else if (item.getDataType() == Cell.CellType.DATE) {
                    typeAttribute = "d";
                    dVal = (Date) item.getValue();
                    value = Double.toString(Helper.getOADate(dVal));
                }
                // String parsing
                else {
                    if (item.getValue() == null) {
                        typeAttribute = "str";
                        value = "";
                    } else // handle shared Strings
                    {
                        //  value = item.getValue().toString();
                        if (item.getDataType() == Cell.CellType.FORMULA) {
                            typeAttribute = "str";
                            value = item.getValue().toString();
                        } else {
                            typeAttribute = "s";
                            value = item.getValue().toString();
                            if (sharedStrings.containsKey(value) == false) {
                                sharedStrings.add(value, Integer.toString(sharedStrings.size()));
                            }
                            value = sharedStrings.get(value);
                            sharedStringsTotalCount++;
                        }
                    }
                    tValue = " t=\"" + typeAttribute + "\" ";
                }
                if (item.getDataType() != Cell.CellType.EMPTY) {
                    sb.append("<c").append(tValue).append("r=\"").append(item.getCellAddress()).append("\"").append(sValue).append(">");
                    if (item.getDataType() == Cell.CellType.FORMULA) {
                        sb.append("<f>").append(escapeXMLChars(item.getValue().toString())).append("</f>");
                    } else {
                        sb.append("<v>").append(escapeXMLChars(value)).append("</v>");
                    }
                    sb.append("</c>");
                } else // Empty cell
                {
                    sb.append("<c").append(tValue).append("r=\"").append(item.getCellAddress()).append("\"").append(sValue).append("/>");
                }
                col++;
            }
            sb.append("</row>");
            return sb.toString();
        }
    }

    private static String escapeXMLChars(String input) {
        int len = input.length();
        List<Integer> illegalCharacters = new ArrayList<>(len);
        List<Integer> characterTypes = new ArrayList<>(len);
        int i;
        char c;
        for (i = 0; i < len; i++) {
            c = input.charAt(i);
            if ((c < 0x9) || (c > 0xA && c < 0xD) || (c > 0xD && c < 0x20) || (c > 0xD7FF && c < 0xE000) || (c > 0xFFFD)) {
                illegalCharacters.add(i);
                characterTypes.add(0);
                continue;
            } // Note: XML specs allow characters up to 0x10FFFF. However, the Java char range is only up to 0xFFFF; Higher values are neglected here
            if (c == 0x3C) // <
            {
                illegalCharacters.add(i);
                characterTypes.add(1);
            } else if (c == 0x3E) // >
            {
                illegalCharacters.add(i);
                characterTypes.add(2);
            } else if (c == 0x26) // &
            {
                illegalCharacters.add(i);
                characterTypes.add(3);
            }
        }
        if (illegalCharacters.isEmpty()) {
            return input;
        }
        StringBuilder sb = new StringBuilder(len);
        int lastIndex = 0;
        len = illegalCharacters.size();
        int j, type;
        for (i = 0; i < len; i++) {
            j = illegalCharacters.get(i);
            type = characterTypes.get(i);
            sb.append(input, lastIndex, j);
            if (type == 0) {
                sb.append(' '); // Whitespace as fall back on illegal character
            } else if (type == 1) // replace <
            {
                sb.append("&lt;");
            } else if (type == 2) // replace >
            {
                sb.append("&gt;");
            } else if (type == 3) // replace &
            {
                sb.append("&amp;");
            }
            lastIndex = j + 1;
        }
        sb.append(input.substring(lastIndex));
        return sb.toString();
    }

    public Document createXMLDocument(String rawInput, String title) {
        return createXMLDocument(rawInput);
    }

    public Document createXMLDocument(String rawInput) {
        try {
            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = factory.newDocumentBuilder();
            InputSource input = new InputSource(new StringReader(rawInput));
            input.setEncoding("UTF-8");
            Document doc = docBuilder.parse(input);
            doc.setXmlVersion("1.0");
            doc.setXmlStandalone(true);
            return doc;
        } catch (Exception e) {
            throw new RuntimeException("There was an error while creating the XML document. Please see the inner exception.", e);
        }
    }

    private Document createAppPropertiesDocument() {
        StringBuilder sb = new StringBuilder();
        sb.append("<Properties xmlns=\"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties\" xmlns:vt=\"http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes\">");
        sb.append(createAppString());
        sb.append("</Properties>");
        return createXMLDocument(sb.toString());
    }

    /**
     * Method to create the XML string for the app-properties document
     *
     * @return String with formatted XML data
     */
    private String createAppString() {
        if (this.workbook.getWorkbookMetadata() == null) {
            return "";
        }
        Metadata md = this.workbook.getWorkbookMetadata();
        StringBuilder sb = new StringBuilder();
        appendXMLtag(sb, "0", "TotalTime", null);
        appendXMLtag(sb, md.getApplication(), "Application", null);
        appendXMLtag(sb, "0", "DocSecurity", null);
        appendXMLtag(sb, "false", "ScaleCrop", null);
        appendXMLtag(sb, md.getManager(), "Manager", null);
        appendXMLtag(sb, md.getCompany(), "Company", null);
        appendXMLtag(sb, "false", "LinksUpToDate", null);
        appendXMLtag(sb, "false", "SharedDoc", null);
        appendXMLtag(sb, md.getHyperlinkBase(), "HyperlinkBase", null);
        appendXMLtag(sb, "false", "HyperlinksChanged", null);
        appendXMLtag(sb, md.getApplicationVersion(), "AppVersion", null);
        return sb.toString();
    }

    private void appendXMLtag(StringBuilder sb, String value, String tagName, String nameSpace) {
        if (Helper.isNullOrEmpty(value)) {
            return;
        }
        if (sb == null || Helper.isNullOrEmpty(tagName)) {
            return;
        }
        boolean hasNoNs = Helper.isNullOrEmpty(nameSpace);
        sb.append('<');
        if (hasNoNs == false) {
            sb.append(nameSpace);
            sb.append(':');
        }
        sb.append(tagName).append(">");
        sb.append(escapeXMLChars(value));
        sb.append("</");
        if (hasNoNs == false) {
            sb.append(nameSpace);
            sb.append(':');
        }
        sb.append(tagName);
        sb.append(">");
    }

    private static String generatePasswordHash(String password) {
        if (Helper.isNullOrEmpty(password)) {
            return "";
        }
        int passwordLength = password.length();
        int passwordHash = 0;
        char character;
        for (int i = passwordLength; i > 0; i--) {
            character = password.charAt(i - 1);
            passwordHash = ((passwordHash >> 14) & 0x01) | ((passwordHash << 1) & 0x7fff);
            passwordHash ^= character;
        }
        passwordHash = ((passwordHash >> 14) & 0x01) | ((passwordHash << 1) & 0x7fff);
        passwordHash ^= (0x8000 | ('N' << 8) | 'K');
        passwordHash ^= passwordLength;
        return Integer.toHexString(passwordHash).toUpperCase();
    }

    private byte[] createRelationshipDocument(Relationship rel) throws IOException {
        StringBuilder sb = new StringBuilder();
        sb.append("<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">\r\n");
        for (int i = 0; i < rel.getIdList().size(); i++) {
            sb.append("<Relationship Target=\"");
            sb.append(rel.getTargetList().get(i));
            sb.append("\" Type=\"");
            sb.append(rel.getTypeList().get(i));
            sb.append("\" Id=\"");
            sb.append(rel.getIdList().get(i));
            sb.append("\"/>\r\n");
        }
        sb.append("</Relationships>");
        Document d = createXMLDocument(sb.toString(), "REL: " + rel.getCurrentId());
        return createBytesFromDocument(d);
    }

    /**
     * Creates the main content type file (MSXML)
     *
     * @return Returns the byte array to add into the compilation
     * @throws ch.rabanti.nanoxlsx4j.exceptions.IOException Thrown if the document could not be converted to a byte array
     */
    private byte[] createContentTypeDocument() throws IOException {
        StringBuilder sb = new StringBuilder();
        sb.append("<Types xmlns=\"http://schemas.openxmlformats.org/package/2006/content-types\">\r\n");
        sb.append("<Default Extension=\"xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml\" />\r\n");
        sb.append("<Default Extension=\"rels\" ContentType=\"application/vnd.openxmlformats-package.relationships+xml\" />\r\n");

        for (int i = 0; i < this.contentTypeList.size(); i++) {
            if (this.includeContentType.get(i) == false) {
                continue;
            }
            sb.append("<Override PartName=\"/");
            sb.append(this.pathList.get(i));
            sb.append("\" ContentType=\"");
            sb.append(this.contentTypeList.get(i));
            sb.append("\" />\r\n");
        }
        sb.append("</Types>");
        Document d = createXMLDocument(sb.toString(), "CONTENTTYPE");
        return createBytesFromDocument(d);
    }
    /**
     * Gets the number of custom number formats
     *
     * @return Number of custom number formats to apply in the style document
     */
    private int getNumberFormatStringCounter() {
        NumberFormat[] numberFormatStyles = this.styles.getNumberFormats();
        int counter = 0;
        for (NumberFormat numberFormatStyle : numberFormatStyles) {
            if (numberFormatStyle.isCustomFormat()) {
                counter++;
            }
        }
        return counter;
    }

    public StyleManager getStyles() {
        return styles;
    }
}
